﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Data;
using Model;
using DBUtility;
using CommonUtility;

namespace DAL
{
    public partial class Message
    {
        #region 增删改
        /// <summary>
        /// 新增
        /// </summary>
        /// <returns></returns>
        public static bool Add(MessageInfo _model)
        {
            if (_model.RID != 0) return Edit(_model, null);
            else return Add(_model, null);
        }

        /// <summary>
        /// 编辑
        /// </summary>
        /// <returns></returns>
        public static bool Edit(MessageInfo _model)
        {
            if (_model.RID == 0) return Add(_model, null);
            else return Edit(_model, null);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <returns></returns>
        public static bool Delete(MessageInfo _model)
        {
            bool res = true;
            try
            {
                string strSQL = "DELETE [tMessage] WHERE [RID]=@RID";
                SqlParameter[] para = new SqlParameter[1];
                para[0] = new SqlParameter("@RID", SqlDbType.Int, 4) { Value = _model.RID };
                int _num = Common.ToInt32(SqlHelper.ExecuteNonQuery(strSQL, para));
                if (_num == 0)
                    res = false;
            }
            catch (Exception ex)
            {
                res = false;
                Common.Application_Error_Server("Message.cs/Delete()", ex);
                throw ex;
            }
            return res;
        }

        #region 带事务
        /// <summary>
        /// 新增
        /// </summary>
        /// <returns></returns>
        public static bool Add(MessageInfo _model, SqlTransaction _myTran)
        {
            if (_model.RID != 0) return Edit(_model, _myTran);
            bool res = true;
            try
            {
                string strSQL = @"
INSERT INTO [tMessage](
	[uid],
	[funID],
	[title],
	[message],
	[cDate],
	[rDate]
)VALUES(
	@uid,
	@funID,
	@title,
	@message,
	@cDate,
	@rDate
);
SELECT @@IDENTITY AS ID;";
                SqlParameter[] para = new SqlParameter[6];
                para[0] = new SqlParameter("@uid", SqlDbType.Int) { Value = _model.Uid };
                para[1] = new SqlParameter("@funID", SqlDbType.VarChar, 4) { Value = _model.FunID };
                para[2] = new SqlParameter("@title", SqlDbType.NVarChar, 200) { Value = _model.Title };
                para[3] = new SqlParameter("@message", SqlDbType.NVarChar, 4000) { Value = _model.Message };
                if (string.IsNullOrEmpty(_model.CDate))
                    para[4] = new SqlParameter("@cDate", SqlDbType.DateTime) { Value = DBNull.Value };
                else if (_model.CDate.Length == 8 && Common.IsNumeric(_model.CDate))
                    para[4] = new SqlParameter("@cDate", SqlDbType.DateTime) { Value = DateTime.ParseExact(_model.CDate, "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture) };
                else
                    para[4] = new SqlParameter("@cDate", SqlDbType.DateTime) { Value = DateTime.Parse(_model.CDate) };
                if (string.IsNullOrEmpty(_model.RDate))
                    para[5] = new SqlParameter("@rDate", SqlDbType.DateTime) { Value = DBNull.Value };
                else if (_model.RDate.Length == 8 && Common.IsNumeric(_model.RDate))
                    para[5] = new SqlParameter("@rDate", SqlDbType.DateTime) { Value = DateTime.ParseExact(_model.RDate, "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture) };
                else
                    para[5] = new SqlParameter("@rDate", SqlDbType.DateTime) { Value = DateTime.Parse(_model.RDate) };

                int _id = Common.ToInt32(_myTran == null ? SqlHelper.ExecuteScalar(strSQL, para) : SqlHelper.ExecuteScalar(strSQL, para, _myTran));
                if (_id == 0)
                    res = false;
                else
                    _model.RID = _id;
            }
            catch (Exception ex)
            {
                res = false;
                Common.Application_Error_Server("Message.cs/Add()", ex);
                throw ex;
            }
            return res;
        }

        /// <summary>
        /// 编辑
        /// </summary>
        /// <returns></returns>
        public static bool Edit(MessageInfo _model, SqlTransaction _myTran)
        {
            if (_model.RID == 0) return Add(_model, _myTran);
            bool res = true;
            try
            {
                string strSQL = @"
UPDATE [tMessage] SET
	[uid]= @uid,
	[funID]= @funID,
	[title]= @title,
	[message]= @message,
	[cDate]= @cDate,
	[rDate]= @rDate
WHERE 
	[RID]= @RID";
                SqlParameter[] para = new SqlParameter[7];
                para[0] = new SqlParameter("@uid", SqlDbType.Int) { Value = _model.Uid };
                para[1] = new SqlParameter("@funID", SqlDbType.VarChar, 4) { Value = _model.FunID };
                para[2] = new SqlParameter("@title", SqlDbType.NVarChar, 200) { Value = _model.Title };
                para[3] = new SqlParameter("@message", SqlDbType.NVarChar, 4000) { Value = _model.Message };
                if (string.IsNullOrEmpty(_model.CDate))
                    para[4] = new SqlParameter("@cDate", SqlDbType.DateTime) { Value = DBNull.Value };
                else if (_model.CDate.Length == 8 && Common.IsNumeric(_model.CDate))
                    para[4] = new SqlParameter("@cDate", SqlDbType.DateTime) { Value = DateTime.ParseExact(_model.CDate, "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture) };
                else
                    para[4] = new SqlParameter("@cDate", SqlDbType.DateTime) { Value = DateTime.Parse(_model.CDate) };
                if (string.IsNullOrEmpty(_model.RDate))
                    para[5] = new SqlParameter("@rDate", SqlDbType.DateTime) { Value = DBNull.Value };
                else if (_model.RDate.Length == 8 && Common.IsNumeric(_model.RDate))
                    para[5] = new SqlParameter("@rDate", SqlDbType.DateTime) { Value = DateTime.ParseExact(_model.RDate, "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture) };
                else
                    para[5] = new SqlParameter("@rDate", SqlDbType.DateTime) { Value = DateTime.Parse(_model.RDate) };
                para[6] = new SqlParameter("@RID", SqlDbType.Int, 4) { Value = _model.RID };

                int _num = Common.ToInt32(_myTran == null ? SqlHelper.ExecuteNonQuery(strSQL, para) : SqlHelper.ExecuteNonQuery(strSQL, para, _myTran));
                if (_num == 0)
                    res = false;
            }
            catch (Exception ex)
            {
                res = false;
                Common.Application_Error_Server("Message.cs/Edit()", ex);
                throw ex;
            }
            return res;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <returns></returns>
        public static bool Delete(MessageInfo _model, SqlTransaction _myTran)
        {
            bool res = true;
            try
            {
                string strSQL = "DELETE [tMessage] WHERE [RID]=@RID";
                SqlParameter[] para = new SqlParameter[1];
                para[0] = new SqlParameter("@RID", SqlDbType.Int, 4) { Value = _model.RID };
                int _num = Common.ToInt32(SqlHelper.ExecuteNonQuery(strSQL, para, _myTran));
                if (_num == 0)
                    res = false;
            }
            catch (Exception ex)
            {
                res = false;
                Common.Application_Error_Server("Message.cs/Delete()", ex);
                throw ex;
            }
            return res;
        }
        #endregion
        #endregion

        /// <summary>
        /// 获取符合条件记录的条数
        /// </summary>
        /// <param name="_condition">条件语句，不包含“where”</param>
        /// <returns></returns>
        public static IList<MessageInfo> GetList(string _condition)
        {
            IList<MessageInfo> list = new List<MessageInfo>();
            try
            {
                string strSQL = @"
SELECT 
	[RID],
	[uid],
	[funID],
	[title],
	[message],
	[cDate],
	[rDate]
FROM [tMessage]";
                if (!string.IsNullOrEmpty(_condition))
                    strSQL += " WHERE " + _condition;

                DataSet mySet = new DataSet();
                SqlHelper.FillDataset(strSQL, mySet, "data", true);
                if (mySet != null && mySet.Tables.Count > 0)
                {
                    foreach (DataRow dr in mySet.Tables["data"].Rows)
                    {
                        MessageInfo model = new MessageInfo();
                        model.RID = Common.ToInt32(dr["RID"]);
                        model.Uid = Common.ToInt32(dr["uid"]);
                        model.FunID = Common.ToString(dr["funID"]);
                        model.Title = Common.ToString(dr["title"]);
                        model.Message = Common.ToString(dr["message"]);
                        model.CDate = Common.ToString(dr["cDate"]);
                        model.RDate = Common.ToString(dr["rDate"]);
                        list.Add(model);
                    }
                }
                mySet.Dispose();
            }
            catch (Exception ex)
            {
                Common.Application_Error_Server("Message.cs/GetList(" + _condition + ")", ex);
            }
            return list;
        }

        /// <summary>
        /// 获取符合条件记录的条数
        /// </summary>
        /// <param name="_condition">条件语句，不包含“where”</param>
        /// <param name="_parms">条件中的参数</param>
        /// <returns></returns>
        public static IList<MessageInfo> GetList(string _condition, SqlParameter[] _parms)
        {
            IList<MessageInfo> list = new List<MessageInfo>();
            try
            {
                string strSQL = @"
SELECT 
	[RID],
	[uid],
	[funID],
	[title],
	[message],
	[cDate],
	[rDate]
FROM [tMessage]";
                if (!string.IsNullOrEmpty(_condition))
                    strSQL += " WHERE " + _condition;

                DataSet mySet = new DataSet();
                SqlHelper.FillDataset(strSQL, mySet, "data", _parms, true);
                if (mySet != null && mySet.Tables.Count > 0)
                {
                    foreach (DataRow dr in mySet.Tables["data"].Rows)
                    {
                        MessageInfo model = new MessageInfo();
                        model.RID = Common.ToInt32(dr["RID"]);
                        model.Uid = Common.ToInt32(dr["uid"]);
                        model.FunID = Common.ToString(dr["funID"]);
                        model.Title = Common.ToString(dr["title"]);
                        model.Message = Common.ToString(dr["message"]);
                        model.CDate = Common.ToString(dr["cDate"]);
                        model.RDate = Common.ToString(dr["rDate"]);
                        list.Add(model);
                    }
                }
                mySet.Dispose();
            }
            catch (Exception ex)
            {
                Common.Application_Error_Server("Message.cs/GetList(" + _condition + ")", ex);
            }
            return list;
        }

        /// <summary>
        /// 根据条件语句取得符合条件的数据表
        /// </summary>
        /// <param name="_condition">条件语句，不包含“where”</param>
        /// <returns></returns>
        public static DataTable GetTable(string _condition)
        {
            DataSet mySet = new DataSet();
            try
            {
                string strSQL = @"
SELECT 
	[RID],
	[uid],
	[funID],
	[title],
	[message],
	[cDate],
	[rDate]
FROM [tMessage]";
                if (!string.IsNullOrEmpty(_condition))
                    strSQL += " WHERE " + _condition;

                SqlHelper.FillDataset(strSQL, mySet, "data", true);
            }
            catch (Exception ex)
            {
                Common.Application_Error_Server("Message.cs/GetTable(" + _condition + ")", ex);
            }
            return mySet != null && mySet.Tables.Count > 0 ? mySet.Tables[0] : null;
        }

        /// <summary>
        /// 获取符合条件记录的条数。
        /// 在使用SQL分页是可以用到，与getTable配合使用
        /// </summary>
        /// <param name="_condition">条件语句，不包含“where”</param>
        /// <returns></returns>
        public static int Count(string _condition)
        {
            int result = 0;
            try
            {
                string strSQL = "SELECT COUNT(*) FROM [tMessage]";
                if (!string.IsNullOrEmpty(_condition))
                    strSQL += " WHERE " + _condition;

                result = Common.ToInt32(SqlHelper.ExecuteScalar(strSQL));
            }
            catch (Exception ex)
            {
                Common.Application_Error_Server("Message.cs/Count(" + _condition + ")", ex);
            }
            return result;
        }

        /// <summary>
        /// 获取分页数据（数据库分页）
        /// </summary>
        /// <param name="_pageIndex">页码，从1开始</param>
        /// <param name="_pageSize">分页大小（每页显示记录数）</param>
        /// <param name="_condition">条件语句，不包含“where”（在组装条件前需要对条件值进行单引号处理）</param>
        /// <param name="_orderStr">排序条件，如：ID DESC</param>
        /// <param name="_total">返回符合条件的总记录数</param>
        /// <param name="_message">存储过程的执行结果</param>
        /// <returns>当前页的数据表，没有数据返回Null</returns>
        public static DataTable GetPageData(int _pageIndex, int _pageSize, string _condition, string _orderStr, ref int _total, ref string _message)
        {
            int recordCount = 0; //本次查到返回的记录数目
            int pageCount = 0; //返回本次查询能够返回的页数
            if (string.IsNullOrEmpty(_orderStr))
                _orderStr = "RID DESC";

            return SqlHelper.GetPageData("tMessage", 0, 1, _condition, _orderStr, "*", _pageIndex, _pageSize, ref _total, ref recordCount, ref pageCount, ref _message);
        }

        /// <summary>
        /// 获取分页数据对象（数据库分页）
        /// </summary>
        /// <param name="_pageIndex">页码，从1开始</param>
        /// <param name="_pageSize">分页大小（每页显示记录数）</param>
        /// <param name="_condition">条件语句，不包含“where”（在组装条件前需要对条件值进行单引号处理）</param>
        /// <param name="_orderStr">排序条件，如：ID DESC</param>
        /// <param name="_total">返回符合条件的总记录数</param>
        /// <param name="_message">存储过程的执行结果</param>
        /// <returns>当前页的数据对象集合</returns>
        public static IList<MessageInfo> GetPageObject(int _pageIndex, int _pageSize, string _condition, string _orderStr, ref int _total, ref string _message)
        {
            IList<MessageInfo> list = new List<MessageInfo>();
            try
            {
                if (string.IsNullOrEmpty(_orderStr))
                    _orderStr = "RID DESC";
                int recordCount = 0; //本次查到返回的记录数目
                int pageCount = 0; //返回本次查询能够返回的页数
                DataTable td = SqlHelper.GetPageData("tMessage", 0, 1, _condition, _orderStr, "*", _pageIndex, _pageSize, ref _total, ref recordCount, ref pageCount, ref _message);
                if (td != null)
                {
                    foreach (DataRow dr in td.Rows)
                    {
                        MessageInfo model = new MessageInfo();
                        model.RID = Common.ToInt32(dr["RID"]);
                        model.Uid = Common.ToInt32(dr["uid"]);
                        model.FunID = Common.ToString(dr["funID"]);
                        model.Title = Common.ToString(dr["title"]);
                        model.Message = Common.ToString(dr["message"]);
                        model.CDate = Common.ToString(dr["cDate"]);
                        model.RDate = Common.ToString(dr["rDate"]);
                        list.Add(model);
                    }
                }
            }
            catch (Exception ex)
            {
                Common.Application_Error_Server("Message.cs/GetPageData(2)", ex);
            }

            return list;
        }

        /// <summary>
        /// 根据ID取得实体
        /// </summary>
        /// <param name="_id">_id,即编号主键</param>
        /// <returns></returns>
        public static MessageInfo GetModelByID(int _id)
        {
            MessageInfo model = null;

            try
            {
                string strSQL = @"
SELECT
	[RID],
	[uid],
	[funID],
	[title],
	[message],
	[cDate],
	[rDate]
FROM [tMessage]
WHERE 
	[RID]= @RID";
                SqlParameter[] para = new SqlParameter[1];
                para[0] = new SqlParameter("@RID", SqlDbType.Int, 4) { Value = _id };
                using (SqlDataReader sdr = SqlHelper.ExecuteReader(strSQL, para))
                {
                    if (sdr.Read())
                    {
                        model = new MessageInfo();
                        model.RID = Common.ToInt32(sdr["RID"]);
                        model.Uid = Common.ToInt32(sdr["uid"]);
                        model.FunID = Common.ToString(sdr["funID"]);
                        model.Title = Common.ToString(sdr["title"]);
                        model.Message = Common.ToString(sdr["message"]);
                        model.CDate = Common.ToString(sdr["cDate"]);
                        model.RDate = Common.ToString(sdr["rDate"]);
                    }
                    sdr.Close();
                }
            }
            catch (Exception ex)
            {
                Common.Application_Error_Server("Message.cs/GetModelByID(" + _id + ")", ex);
            }
            return model;
        }
    }
}